package dao;

import entity.Products;
import utils.DBUtils;

import java.sql.*;
import java.util.*;

/**
 * Created by Administrator on 2016/4/19 0019.
 */
public class ProductsDAO {
    /**
     * 查询总共有多少条数据
     * @return
     */
    public Integer queryAllCount() {
        int count = 0;
        String sql = "SELECT COUNT(1) FROM PRODUCTS";
        Connection conn = DBUtils.getConn();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            if ( rs.next() ){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn, pst);
        }
        return count;
    }

    /**
     * 通过分页数据查询当前页的数据
     * @param pageNum
     * @param pageCount
     * @return
     */
    public List<Products> queryPagesProducts( int pageNum, int pageCount ) {
        List<Products> productses = null;
        String sql = "SELECT * FROM PRODUCTS WHERE ROWNUM <= " + pageCount + " AND ROWID NOT IN (SELECT ROWID FROM PRODUCTS WHERE ROWNUM <= " + (pageNum - 1) * pageCount + ")";
        Connection conn = DBUtils.getConn();
        // PreparedStatement pst = null;
        Statement pst = null;
        try {
            //pst = conn.prepareStatement(sql);
            //pst.setInt(1,pageCount);
            //pst.setInt(2,(pageNum - 1) * pageCount);
            //ResultSet rs = pst.executeQuery();
            pst = conn.createStatement();
            System.out.println(sql);
            ResultSet rs = pst.executeQuery(sql);
            productses = new ArrayList<>();
            while ( rs.next() ){
                Products pro = new Products();
                pro.setId(rs.getInt(1));
                pro.setName(rs.getString(2));
                pro.setPrice(rs.getDouble(3));
                pro.setCount(rs.getInt(4));
                System.out.println("------------------");
                productses.add(pro);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn, pst);
        }
        return productses;
    }
}
